package cn.wh.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.beans.PropertyVetoException;
import java.sql.*;
import java.util.*;

public class DBUtils {

    private static ComboPooledDataSource o_pool = new ComboPooledDataSource();
    private static ComboPooledDataSource m_pool = new ComboPooledDataSource();
    private static final Logger LOGGER = LoggerFactory.getLogger(DBUtils.class.getName());
    static {
        try {
            o_pool.setDriverClass(ProperUtils.getProperty("o_jdbc.driver"));
            o_pool.setJdbcUrl(ProperUtils.getProperty("o_jdbc.url"));
            o_pool.setUser(ProperUtils.getProperty("o_jdbc.username"));
            o_pool.setPassword(ProperUtils.getProperty("o_jdbc.password"));
            o_pool.setInitialPoolSize(2);
            o_pool.setMaxPoolSize(5);
            o_pool.setCheckoutTimeout(60000);

            m_pool.setDriverClass(ProperUtils.getProperty("m_jdbc.driver"));
            m_pool.setJdbcUrl(ProperUtils.getProperty("m_jdbc.url"));
            m_pool.setUser(ProperUtils.getProperty("m_jdbc.username"));
            m_pool.setPassword(ProperUtils.getProperty("m_jdbc.password"));
            m_pool.setInitialPoolSize(2);
            m_pool.setMaxPoolSize(5);
            m_pool.setCheckoutTimeout(60000);
        } catch (PropertyVetoException e) {
            LOGGER.error("初始化链接数据源失败，异常为:{}",e);
            e.printStackTrace();
        }
    }

    public static Connection getOracleConnection(){
        try {
            return  o_pool.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    public static Connection getMySqlConnection(){
        try {
            return  m_pool.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    /***
     * 根据Sql与连接源查询结果并返回
     * @Author wanghao
     * @Date 14:53 2018/12/29
     * @param sql
     * @return java.util.List<java.util.Map<java.lang.String,java.lang.String>>
     **/

    public static List<Map<String,String>> querySql(String sql ,Connection conn){
        if(LOGGER.isDebugEnabled()){
            LOGGER.debug("Enter 执行SQL语句：SQL:{},Connection:{}",sql,conn);
        }
        List<Map<String,String>> mapList = new ArrayList<Map<String, String>>();
        Statement state=null ;
        if(conn==null){
            return mapList;
        }
        try {
            state =conn.createStatement();
            ResultSet res = state.executeQuery(sql);
            ResultSetMetaData rsmd = res.getMetaData();
            int count=rsmd.getColumnCount();
            while (res.next()){
                Map<String,String> map = new HashMap<String, String>();
                for (int i = 0; i < count; i++) {
                    String name =rsmd.getColumnName(i+1);
                    map.put(name,res.getString(name));
                }
                mapList.add(map);
            }
        } catch (SQLException e) {
            LOGGER.error(" 查询SQL出现异常：SQL:{},Conn:{}e:{}",sql,conn,e);
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            if(state!=null){
                try {
                    state.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
        if(LOGGER.isDebugEnabled()){
            LOGGER.debug("Exit 执行SQL语句,返回结果：{}",mapList);
        }
        return mapList;
    }

    public static int queryCountSql(String sql ,Connection conn){
        if(LOGGER.isDebugEnabled()){
            LOGGER.debug("Enter 执行SQL语句：SQL:{},Connection:{}",sql,conn);
        }
        int i = 0;
        Statement state=null ;
        if(conn==null){
            return i;
        }
        try {
            state =conn.createStatement();
            ResultSet res = state.executeQuery(sql);
            if(res.next()){
                i= res.getInt(1);
            }
        } catch (SQLException e) {
            LOGGER.error(" 查询SQL出现异常：SQL:{},Conn:{}e:{}",sql,conn,e);

        }finally {
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            if(state!=null){
                try {
                    state.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
        if(LOGGER.isDebugEnabled()){
            LOGGER.debug("Exit 执行SQL语句,返回结果：{}",i);
        }
        return i;
    }



    /***
     * 根据Sql与连接源查询结果并返回
     * @Author wanghao
     * @Date 14:53 2018/12/29
     * @param sql
     * @return java.util.List<java.util.Map<java.lang.String,java.lang.String>>
     **/

    public static List<Map<String,String>> queryMySql(String sql){
        List<Map<String,String>> mapList = new ArrayList<Map<String, String>>();
        Statement state=null ;
        Connection con = getMySqlConnection();
        if(con==null){
            return mapList;
        }
        try {
            state =con.createStatement();
            ResultSet res = state.executeQuery(sql);
            while (res.next()){
                Map<String,String> map = new HashMap<String, String>();
                map.put("ue_id",res.getString("ue_id"));
                map.put("neid",res.getString("neid"));
                map.put("serv_cell_ci",res.getString("serv_cell_ci"));
                map.put("count",res.getString("count"));
                map.put("min_serv_ecio",res.getString("min_serv_ecio"));
                map.put("min_serv_rscp_dbm",res.getString("min_serv_rscp_dbm"));
                map.put("gt90",res.getString("gt90"));
                map.put("gt100",res.getString("gt100"));
                map.put("lt100",res.getString("lt100"));
                mapList.add(map);
            }
        } catch (SQLException e) {

        }finally {
            if(con!=null){
                try {
                    con.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            if(state!=null){
                try {
                    state.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
        return mapList;
    }

    /**
     * 插入到mr_analysis中
     * @param mapList
     * @param time_s
     * @return
     */
    public static void insertMrAnalysis(List<Map<String,String>> mapList,String time_s){
        if(LOGGER.isDebugEnabled()){
            LOGGER.debug("Enter 执行SQL语句：SQL:{},time_s:{}",mapList,time_s);
        }
        Connection con = getOracleConnection();
        PreparedStatement ps = null;
        if(con == null){
            return ;
        }

        try {
            con.setAutoCommit(false);
            ps = con.prepareStatement("INSERT INTO irms.mr_analysis (NEID, UE_ID, SERV_CELL_CI, COUNT, MIN_SERV_ECIO, MIN_SERV_RSCP_DBM, GT90, GT100, LT100,time_s,createTime)  " +
                    " VALUES (?, ?, ?, ?, ?, ?, ?, ?,?,?,sysdate) ");
            int i = 0;
            for (Map<String, String> map :
                    mapList) {
                if(map==null){
                    continue;
                }
                ps.setString(1, map.get("neid"));
                ps.setString(2, map.get("ue_id"));
                ps.setString(3, map.get("serv_cell_ci"));
                ps.setString(4, map.get("count"));
                ps.setString(5, map.get("min_serv_ecio"));
                ps.setString(6, map.get("min_serv_rscp_dbm"));
                ps.setString(7, map.get("gt90"));
                ps.setString(8, map.get("gt100"));
                ps.setString(9, map.get("lt100"));
                ps.setString(10, time_s);
                ps.addBatch();
                i++;
                if(i==100){
                    int [] is = ps.executeBatch();
                    con.commit();
                    if(LOGGER.isDebugEnabled()){
                        LOGGER.debug("插入成功：res:{}", Arrays.toString(is));
                    }
                    i=0;
                }
            }
            int [] is=ps.executeBatch();
            con.commit();
            if(LOGGER.isDebugEnabled()){
                LOGGER.debug("插入成功：res:{}", Arrays.toString(is));
            }
            con.setAutoCommit(true);

        } catch (SQLException e) {

        }finally {
            if(con!=null){
                try {
                    con.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            if(ps !=null){
                try {
                    ps.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
    }



    public static Connection getCon(){
        Connection conn = null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            String dbURL = "jdbc:oracle:thin:@localhost:1521:orcl";
            conn = DriverManager.getConnection(dbURL, "irms", "irms1#");
            System.out.println("连接成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;

    }
    public static void main(String[] args) {
        // System.out.println(getOracleConnection());
        // System.out.println(getMySqlConnection());
        String sql = "select ue_id,neid,serv_cell_ci ,count(1) as count ,min(serv_ecio) as min_serv_ecio,min(serv_rscp_dbm) as min_serv_rscp_dbm,  " +
                "sum(case WHEN serv_rscp_dbm>-90  THEN 1  else 0 end ) gt90 ,  " +
                "sum(case WHEN serv_rscp_dbm>-100  and serv_rscp_dbm<-90  THEN 1  else 0 end ) gt100 ,   " +
                "sum(case WHEN serv_rscp_dbm<-100  THEN 1  else 0 end ) lt100    " +
                "from mr_intrainterfreq  where  time_s>=STR_TO_DATE('2018-07-17 00:00:00','%Y-%m-%d %H:%i:%s')   " +
                " and time_s<=STR_TO_DATE('2018-07-17 23:59:59','%Y-%m-%d %H:%i:%s') and ue_id is not null  " +
                " group by ue_id,neid,serv_cell_ci  ";

//        String Oraclesql = "select * from irms.mr_analysis  ";
//        System.out.println(querySql(sql,getMySqlConnection()));
//        System.out.println(querySql(Oraclesql,getOracleConnection()));

        // insertMrAnalysis(queryMySql(sql),"2018-07-17");
        System.out.println(queryCountSql(" select count(*) from ( "+sql+"  ) s",getMySqlConnection()));

    }
}
